# coding:utf-8
# 存储管道
import sqlite3


class SqlitePipeline(object):

    def __init__(self):
        self.con = sqlite3.connect('db.sqlite')
        self.cursor = self.con.cursor()

    def close_spider(self, spider):
        self.cursor.close()
        self.con.close()
        self.cursor = None
        self.con = None

    def process_item(self, item, spider):
        last_create_table_sql = ''
        insert_columns = []
        insert_values = []
        for key, value in item.items():
            if isinstance(value, int):
                last_create_table_sql += str(key) + ' INTEGER,'
            elif isinstance(value, str):
                last_create_table_sql += key + ' VARCHAR(255),'
            elif isinstance(value, float):
                last_create_table_sql += str(key) + ' FLOAT,'
            else:
                last_create_table_sql += str(key) + ' VARCHAR(255),'
            # insert_columns
            insert_columns.append(key)
            # insert_values
            insert_values.append(value)

        sql = f"""
            CREATE TABLE IF NOT EXISTS {spider.name} (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                {last_create_table_sql[:-1]}
            );
        """
        self.cursor.execute(sql)
        # 插入
        sql = f"""
            INSERT INTO {spider.name}({",".join(insert_columns)}) VALUES ({('?,'*len(insert_values))[:-1]});
        """
        self.cursor.execute(sql, insert_values)
        self.con.commit()
        return item